﻿using System.Data;
using Aspose.Cells;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Presentation;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Wordprocessing;
using Cell = DocumentFormat.OpenXml.Spreadsheet.Cell;
using Row = DocumentFormat.OpenXml.Spreadsheet.Row;
using Workbook = DocumentFormat.OpenXml.Spreadsheet.Workbook;
using Worksheet = DocumentFormat.OpenXml.Spreadsheet.Worksheet;

namespace ConsoleApp;

public class DemoOpenXml
{
    public static void Main()
    {
        var filePath = @"D:\2303142.xls";
        
        // var list = ReadExcel(@"D:\demo.xlsx");
        // foreach (DataRow item in list.Rows)
        // {
        //     Console.WriteLine(JsonConvert.SerializeObject(item));
        // }

        //WriteExcel(filePath);

        ConvertXlsToXlsx(filePath);
    }
    
    public static void ConvertXlsToXlsx(string sourceFilePath)
    {
        try
        {
            using (var sourceDocument = SpreadsheetDocument.Open(sourceFilePath, true))
            {
                // 将.xls转换为.xlsx实质上是将一个SpreadsheetDocument保存为SpreadsheetDocument120格式
                // 因此，我们只需要保存为新的文件格式即可
                sourceDocument.ChangeDocumentType(SpreadsheetDocumentType.Workbook);
                //sourceDocument
            }
 
            Console.WriteLine("转换完成.");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"转换失败: {ex.Message}");
        }
    }
    
    /// <summary>
    /// 读取Excel
    /// </summary>
    /// <param name="filePath"></param>
    /// <returns></returns>
    public static DataTable ReadExcel(string filePath)
    {
        var dataTable = new DataTable();
        using var spreadsheetDocument = SpreadsheetDocument.Open(filePath, false);
        // 获取工作表集合
        var workbookPart = spreadsheetDocument.WorkbookPart;
        if (workbookPart == null)
        {
            return dataTable;
        }

        var sheets = workbookPart.Workbook.Descendants<Sheets>().FirstOrDefault();
        if (sheets == null)
        {
            return dataTable;
        }
        
        foreach (var sheet in sheets.Elements<Sheet>())
        {
            var id = sheet.Id?.ToString();
            if (id == null)
            {
                break;
            }
            
            // 获取工作表内容
            var worksheetPart = (WorksheetPart)workbookPart.GetPartById(id);
            var workSheet = worksheetPart.Worksheet;
            var sheetData = workSheet.GetFirstChild<SheetData>();
            if (sheetData == null)
            {
                return dataTable;
            }
            var row = sheetData.Descendants<Row>().FirstOrDefault();
            if (row == null)
            {
                return dataTable;
            }
            
            foreach (var cell in row.Descendants<Cell>())
            {
                dataTable.Columns.Add(GetCellValue(cell, workbookPart));
            }
            
            foreach (var dataRow in sheetData.Descendants<Row>().Skip(1))
            {
                var newRow = dataTable.NewRow();
                var i = 0;
                foreach (var cell in dataRow.Descendants<Cell>())
                {
                    newRow[i] = GetCellValue(cell, workbookPart);
                    i++;
                }
                dataTable.Rows.Add(newRow);
            }
            
            // 获取工作表中的单元格
            // var cells = worksheetPart.Worksheet.Descendants<Cell>();
            // foreach (var cell in cells)
            // {
            //     // 获取单元格的值
            //     var cellValue = GetCellValue(cell, workbookPart);
            //     if (cellValue != "")
            //         dataTable.Columns.Add(cellValue);
            //     Console.WriteLine(cellValue);
            // }
        }

        return dataTable;
    }

    private static string? GetCellValue(Cell cell, WorkbookPart workbookPart)
    {
        var value = string.Empty;

        if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
        {
            var sharedStringIndex = int.Parse(cell.InnerText);
            value = workbookPart.SharedStringTablePart?.SharedStringTable.ChildElements[sharedStringIndex].InnerText;
        }
        else if (cell.CellValue != null)
        {
            value = cell.CellValue.Text;
        }

        return value;
    }

    public static void WriteExcel(string filePath)
    {
        // 创建一个新的Excel文件
        using var spreadSheet = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook);
        // 添加一个Workbook部分
        var workbookPart = spreadSheet.AddWorkbookPart();
        workbookPart.Workbook = new Workbook();

        // 添加一个Worksheet部分
        var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
        worksheetPart.Worksheet = new Worksheet(new SheetData());
        var sheet = new Sheet { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" };

        // 将Worksheet添加到Workbook
        workbookPart.Workbook.Append(new Sheets(sheet));

        // 写入数据
        var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
        var row = new Row { RowIndex = (UInt32Value)1U };

        // 在单元格中添加数据
        var cell = new Cell { CellReference = "A1", DataType = CellValues.String };
        cell.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Text("Hello OpenXml"));
        row.AppendChild(cell);

        sheetData?.AppendChild(row);

        // 保存文档
        worksheetPart.Worksheet.Save();
        spreadSheet.Clone();
    }

    /// <summary>
    /// 读取Word
    /// </summary>
    /// <param name="fileName"></param>
    /// <returns></returns>
    public static List<string> ReadWord(string fileName)
    {
        try
        {
            var list = new List<string>();
            // 打开现有的Word文档
            using var doc = WordprocessingDocument.Open(fileName, false);
            // 获取文档的主体部分
            var body = doc.MainDocumentPart.Document.Body;
            // 遍历所有段落并输出文本
            foreach (var para in body.Elements<Paragraph>())
            {
                if (para.InnerText == null && para.InnerText == "") continue;
                list.Add(para.InnerText);
                Console.WriteLine(para.InnerText);
            }

            return list;
        }
        catch (Exception ex)
        {
            Console.WriteLine("读取Word失败：" + ex.Message);
            return null;
        }
    }

    /// <summary>
    /// 读取PPT
    /// </summary>
    /// <param name="fileName"></param>
    /// <returns></returns>
    public static List<string> ReadPpt(string fileName)
    {
        try
        {
            var list = new List<string>();
            // 打开现有的ppt文档
            using var ppt = PresentationDocument.Open(fileName, false);
            // 获取文档的主体部分
            foreach (var slideId in ppt.PresentationPart.Presentation.SlideIdList.Elements<SlideId>())
            {
                // 获取幻灯片内容
                var slidePart = (SlidePart)ppt.PresentationPart.GetPartById(slideId.RelationshipId);

                // 获取幻灯片中的文本框
                var textBoxes = slidePart.Slide.Descendants<DocumentFormat.OpenXml.Drawing.Text>();

                foreach (var textBox in textBoxes)
                {
                    Console.WriteLine(textBox.Text);
                    list.Add(textBox.Text);
                }
            }

            return list;
        }
        catch (Exception ex)
        {
            Console.WriteLine("读取Word失败：" + ex.Message);
            return null;
        }
    }
}